import frappe
from frappe import _

from frappe.model.meta import get_field_precision
from erpnext import get_default_currency    
from frappe.query_builder.functions import Coalesce, Round
from frappe.query_builder import Case
from pypika import Order    
from datetime import datetime, timedelta
from frappe.utils import flt, cint

def execute(filters=None):
    columns = get_column()
    data = get_ordered_to_be_billed_data(filters)
    return columns, data

def get_column():
    return [
        {"label": _("Posting Date"), "fieldname": "posting_date", "fieldtype": "Date", "width": 130},
        {
            "label": _("Delivery Note"),
            "fieldname": "delivery_note",
            "fieldtype": "Link",
            "options": "Delivery Note",
            "width": 160,
        },
        {
            "label": _("Customer's Purchase Order"),
            "fieldname": "po_no",
            "fieldtype": "Data",            
            "width": 120,
        },
        {
            "label": _("Customer"),
            "fieldname": "customer",
            "fieldtype": "Link",
            "options": "Customer",
            "width": 120,
        },
        {"label": _("Customer Name"), "fieldname": "customer_name", "fieldtype": "Data", "width": 120},
        {
            "label": _("Item Code"),
            "fieldname": "item_code",
            "fieldtype": "Link",
            "options": "Item",
            "width": 240,
        },
        {
            "label": _("Is Rework"),
            "fieldname": "custom_是否返工",
            "fieldtype": "Data",
            "width": 90
        },
        {
            "label": _("Is Sapmle"),
            "fieldname": "custom_是否样品",
            "fieldtype": "Data",
            "width": 90
        },
        {
            "label": _("Qty"),
            "fieldname": "qty",
            "fieldtype": "Float",
            "width": 80
        },
        {
            "label": _("Rate"),
            "fieldname": "rate",
            "fieldtype": "Float",
            "width": 100
        },
        {
            "label": _("Amount"),
            "fieldname": "amount",
            "fieldtype": "Currency",
            "width": 100,
            "editable": True,
            "options": "Company:company:default_currency",
        },                
        {
            "label": _("Billed Amount"),
            "fieldname": "billed_amount",
            "fieldtype": "Currency",
            "width": 100,
            "options": "Company:company:default_currency",
        },
        {
            "label": _("Returned Amount"),
            "fieldname": "returned_amount",
            "fieldtype": "Currency",
            "width": 120,
            "options": "Company:company:default_currency",
        },
        {
            "label": _("Billable Amount"),
            "fieldname": "billable_amount",
            "fieldtype": "Currency",
            "width": 120,
            "options": "Company:company:default_currency",
        },
        # 报表添加自定义字段不支持子单据类型
        #{"label": _("Delivery Note Item"), "fieldname": "dn_detail", "fieldtype": "Link", "options":"Delivery Note Item", "hidden": 0},
        # {"label": _("Customer's Item Code"), "fieldname": "customer_item_code", "fieldtype": "Data", "width": 120},
        # {"label": _("Item Name"), "fieldname": "item_name", "fieldtype": "Data", "width": 120},
        # {"label": _("Description"), "fieldname": "description", "fieldtype": "Data", "width": 120},
        # {
        #     "label": _("Project"),
        #     "fieldname": "project",
        #     "fieldtype": "Link",
        #     "options": "Project",
        #     "width": 120,
        # },
        # {"label": _("Is Return"), "fieldname": "is_return", "fieldtype": "Check", "width": 60},
        {
            "label": _("Company"),
            "fieldname": "company",
            "fieldtype": "Link",
            "options": "Company",
            "width": 120,
        },
        {            
            "fieldname": "child_name",
            "fieldtype": "Data",
            "hidden": 1
        }
    ]

def get_ordered_to_be_billed_data(filters):
    """
    改为pypika
    name 改为delivery_note字段名
    加childname字段
    添加剔除草稿状态中的出库明细
    """

    precision = (
        get_field_precision(
            frappe.get_meta('Delivery Note Item').get_field("billed_amt"), currency=get_default_currency()
        )
        or 2
    )
    
    dn = frappe.qb.DocType('Delivery Note')
    dni = frappe.qb.DocType('Delivery Note Item')
    dni_meta = frappe.get_meta("Delivery Note Item")

    query = frappe.qb.from_(dn
    ).join(dni
    ).on(dn.name == dni.parent
    ).select(
        dn.name.as_('delivery_note'),
        dn.is_return,
        dn.posting_date,
        dn.customer,
        dn.customer_name,
        #dni.name.as_("dn_detail"),
        dni.item_code,
        dni.customer_item_code,
        dni.qty,
        dni.rate,
        dni.base_amount.as_('amount'),
        (dni.billed_amt * Coalesce(dn.conversion_rate, 1)).as_('billed_amount'),
        (dni.base_rate * Coalesce(dni.returned_qty, 0)).as_('returned_amount'),
        (dni.base_amount -
            (dni.billed_amt * Coalesce(dn.conversion_rate, 1)) -
            (dni.base_rate * Coalesce(dni.returned_qty, 0))).as_('billable_amount'),
        dni.item_name,
        dni.description,
        dn.project,
        dn.company,
        dni.name.as_('child_name'),
        dni.custom_是否返工,
        dni.custom_是否样品
    ).where(
        (dn.company==filters.get('company')) &
        (dn.posting_date.between(filters.get('from_date'), filters.get('to_date'))) &
        (dn.docstatus == 1) &
        (dn.status.notin(['Closed', 'Completed']))
    )
    # .orderby(dn.name, Order.desc)
    # delivery_category = filters.delivery_category
    # if delivery_category:
    #     is_return = delivery_category == 'Return Delivery'
    #     query = query.where(dn.is_return == is_return)

    exclude_in_draft_invoice = filters.get('exclude_in_draft_invoice')
    if exclude_in_draft_invoice:
        sii = frappe.qb.DocType('Sales Invoice Item')
        query = query.left_join(sii
        ).on((sii.dn_detail == dni.name) & 
            (sii.docstatus == 0) 
        ).where(sii.name.isnull())

    customer = filters.get('customer')
    if customer:
        query = query.where(dn.customer==customer)

    # query = query.where(
    # #         # 如果可开票金额大于0，或者是返工且未开票的记录
    #         (
    #             (dni.base_amount - 
    #                 Round(dni.billed_amt * Coalesce(dn.conversion_rate, 1), precision) - 
    #                 (dni.base_rate * Coalesce(dni.returned_qty, 0)) >= 0
    #             )
    #         )
    #     )

    billable_pos = (
        (dni.base_amount
        - Round(dni.billed_amt * Coalesce(dn.conversion_rate, 1), precision)
        - (dni.base_rate * Coalesce(dni.returned_qty, 0))) > 0
    )
    zero_rate = (Coalesce(dni.rate, 0) == 0)

    # 基础条件：放行“可开票>0”或“单价=0”
    query = query.where(billable_pos | zero_rate)

    # rework_filter = filters.rework_filter

    exclude_rework = bool(cint(filters.get("exclude_rework")))
    include_past_rework = bool(cint(filters.get("include_past_rework")))

    # if rework_filter:
    #     query = query.where(
    #         # 如果可开票金额大于0，或者是返工且未开票的记录
    #         (
    #             (dni.base_amount - 
    #                 Round(dni.billed_amt * Coalesce(dn.conversion_rate, 1), precision) - 
    #                 (dni.base_rate * Coalesce(dni.returned_qty, 0)) >= 0
    #             )
    #         )
    #     )
    # else:
    #     query = query.where(
    #         (
    #             (dni.base_amount - 
    #                 Round(dni.billed_amt * Coalesce(dn.conversion_rate, 1), precision) - 
    #                 (dni.base_rate * Coalesce(dni.returned_qty, 0)) >= 0
    #             )
    #         )
    #     )

    if exclude_rework:
        # 排除返工单
        three_months_ago = (datetime.today() - timedelta(days=90)).strftime('%Y-%m-%d')
        linked_delivery_notes = frappe.get_all('Sales Order Item', filters={'custom_是否返工': '是', 'modified': ['>', three_months_ago]}, fields=['custom_source_delivery_note'])
        linked_delivery_note_names = [dn['custom_source_delivery_note'] for dn in linked_delivery_notes if dn['custom_source_delivery_note'] is not None]

        # frappe.msgprint(f"Linked Delivery Notes: {linked_delivery_notes}")

        if linked_delivery_note_names:
            query = query.where(
                dn.name.notin(linked_delivery_note_names)
            )

    if include_past_rework:
        # 带出往期返工单
        query_past_rework = frappe.qb.from_(dn
        ).join(dni
        ).on(dn.name == dni.parent
        ).select(
            dn.name.as_('delivery_note'),
            dn.is_return,
            dn.posting_date,
            dn.customer,
            dn.customer_name,
            #dni.name.as_("dn_detail"),
            dni.item_code,
            dni.customer_item_code,
            dni.qty,
            dni.rate,
            dni.base_amount.as_('amount'),
            (dni.billed_amt * Coalesce(dn.conversion_rate, 1)).as_('billed_amount'),
            (dni.base_rate * Coalesce(dni.returned_qty, 0)).as_('returned_amount'),
            (dni.base_amount -
                (dni.billed_amt * Coalesce(dn.conversion_rate, 1)) -
                (dni.base_rate * Coalesce(dni.returned_qty, 0))).as_('billable_amount'),
            dni.item_name,
            dni.description,
            dn.project,
            dn.company,
            dni.name.as_('child_name'),
            dni.custom_是否返工,
            dni.custom_是否样品
        ).where(
            (dn.company==filters.get('company')) &
            (dn.posting_date < filters.get('from_date')) &
            (dn.docstatus == 1) &
            (dn.status.notin(['Closed', 'Completed'])) &
            (billable_pos | zero_rate)
            # (dni.base_amount - 
            #         Round(dni.billed_amt * Coalesce(dn.conversion_rate, 1), precision) - 
            #         (dni.base_rate * Coalesce(dni.returned_qty, 0)) >= 0
            #     )
        )
        exclude_in_draft_invoice = filters.get('exclude_in_draft_invoice')
        if exclude_in_draft_invoice:
            sii = frappe.qb.DocType('Sales Invoice Item')
            query_past_rework = query_past_rework.left_join(sii
            ).on((sii.dn_detail == dni.name) & 
                (sii.docstatus == 0) 
            ).where(sii.name.isnull())
        if customer:
            query_past_rework = query_past_rework.where(dn.customer==customer)
        query = query.union_all(query_past_rework).orderby('delivery_note', Order.desc)

    rows = query.run(as_dict=1) or []

    # 找出需要补价的物料
    def is_normal_item(r):
    # 仅对“非返工、非样品”的行补价
        return r.get("custom_是否返工") != "是" and r.get("custom_是否样品") != "是"

    # 仅收集 rate=0 且 非返工/非样品 的物料
    need_price_codes = {r["item_code"] for r in rows if not flt(r.get("rate")) and is_normal_item(r)}
    if not need_price_codes:
        return rows

    price_list = (
        filters.get("price_list")
        or frappe.db.get_single_value("Selling Settings", "selling_price_list")
        or "标准销售"
    )

    price_rows = frappe.get_all(
        "Item Price",
        filters={"selling": 1, "price_list": price_list, "item_code": ["in", list(need_price_codes)]},
        fields=["item_code", "price_list_rate", "valid_from", "modified"],
        order_by="COALESCE(valid_from, '1900-01-01') DESC, modified DESC",
    )

    price_map = {}
    for pr in price_rows:
        price_map.setdefault(pr["item_code"], flt(pr["price_list_rate"]))

    # 回填：只处理 非返工/非样品 且 rate=0 的行
    for r in rows:
        if not flt(r.get("rate")) and is_normal_item(r):
            new_rate = price_map.get(r["item_code"])
            if new_rate:
                r["rate"] = new_rate
                if r.get("qty") is not None and "amount" in r:
                    r["amount"] = round(flt(r["qty"]) * new_rate, 2)

    return rows


"""
for test
filters = {"company":"则霖信息技术（深圳）有限公司","from_date":"2024-03-30","to_date":"2024-04-30","po_no":"4502\n4501\n4503","exclude_in_draft_invoice":1}
from zelin_ac.zelin_accounting.report.delivery_notes_to_bill.delivery_notes_to_bill import *


"""